# !/usr/bin/env python3
# coding=utf8
"""
sqlite 里的 指定数据表添加"insert_time"列,
"""
import pathlib
import sqlite3
from typing import Dict, List, Set, Tuple, Optional, Union, Callable

from vnpy.trader.utility import get_file_path


class TableModifier(object):
    """
    SQLite 的表修改器
    """

    DBPATH: pathlib.Path = get_file_path("database.db")

    @classmethod
    def get_table_name_s(cls) -> List[str]:
        """"""
        # 查看 dbbardata 的表结构: PRAGMA table_info(dbbardata);

        sql_statement: str = "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;"

        with sqlite3.connect(database=cls.DBPATH) as connection:
            cursor: sqlite3.Cursor = connection.cursor()
            cursor.execute(sql_statement)

            head: List[str] = [col[0] for col in cursor.description]
            results: List[dict] = [dict(zip(head, data)) for data in cursor.fetchall()]

            table_name_s: List[str] = [row["name"] for row in results]

            return table_name_s

    @classmethod
    def add_column_insert_time(cls, table_name: str) -> None:
        """"""
        # https://stackoverflow.com/questions/381371/sqlite-current-timestamp-is-in-gmt-not-the-timezone-of-the-machine
        # https://www.sqlite.org/lang_datefunc.html
        with sqlite3.connect(database=cls.DBPATH) as connection:
            cursor: sqlite3.Cursor = connection.execute(f"PRAGMA table_info({table_name})")

            head: List[str] = [col[0] for col in cursor.description]
            results: List[dict] = [dict(zip(head, data)) for data in cursor.fetchall()]

            columns: List[str] = [row["name"] for row in results]

            if "insert_time" not in columns:
                sql: str = f"""ALTER TABLE "{table_name}" ADD COLUMN "insert_time" DATETIME NOT NULL DEFAULT (datetime('now', 'localtime') );"""
                connection.execute(sql)

    @classmethod
    def execute(cls) -> None:
        """"""
        table_name_s: List[str] = [
            "dbbacktestdata",
            "dbbardata",
            "dbbaroverview",
            "dbtickdata",
            "dbtickoverview",
        ]

        intersection: Set[str] = set(cls.get_table_name_s()).intersection(set(table_name_s))

        for table_name in intersection:
            cls.add_column_insert_time(table_name)


if __name__ == "__main__":
    TableModifier.execute()
